package com.supermap.wzhy.common.service;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * Excel操作公共服务抽象类
 *
 * @author Created by Administrator on 14-3-7.
 */
public abstract class ExcelOperateServices<T, S> extends BaseService {

    /**
     * 读取解析excel文件表单的List数据对象
     *
     * @param path
     *            excel文件路径（包含了文件名）
     * @param sheetName
     *            表单名
     * @param isContainHeader
     *            是否有一行表头
     * @return Excel二维数据列表
     */
    public List<List<Object>> readExcel(String path, String sheetName,
                                        boolean isContainHeader) {
        String extStr = getExtensionName(path);// 扩展名
        if (extStr.toLowerCase().equals("xls")) {
            return readExcelLowVersion(path, sheetName, isContainHeader);
        } else if (extStr.toLowerCase().equals("xlsx")) {
            return readExcelAdvabceVersion(path, sheetName, isContainHeader);
        } else {
            return null;
        }
    }

    /**
     * 读取excel文件表头
     * @param path
     *            excel文件路径（包含了文件名）
     * @param sheetName
     *            表单名
     * @return 文件表头（可能返回null）
     */
    public String[] readExcelHeader(String path, String sheetName) {
        String extStr = getExtensionName(path);
        if (extStr.toLowerCase().equals("xls")) {
            return readExcelLowVersionHeader(path, sheetName);
        } else if (extStr.toLowerCase().equals("xlsx")) {
            return readExcelAdvabceVersionHeader(path, sheetName);
        } else {
            return null;
        }
    }

    /**
     * 导出列表数据带excel中
     *
     * @param path
     *        	导出的excel文件路径（包含了文件名）
     * @param sheetName
     *          导出的excel表单名
     * @param head
     * 			excel的表头
     * @param detalist
     * 			导出的具体数据
     * @return 导出结果
     */
    public boolean exportExcel(String path, String sheetName, String[] head,
                               List<List<String>> detalist) {
        String extStr = getExtensionName(path);
        if (extStr.toLowerCase().equals("xls")) {
            return exportExcelLowVersion(path, sheetName, head, detalist);
        } else if (extStr.toLowerCase().equals("xlsx")) {
            return exportExcelAdvanceVersion(path, sheetName, head, detalist);
        } else {
            return false;
        }
    }

    /**
     * excel文件中表头的读取
     * <p style="color:red;font-size:14px;">
     * &nbsp;&nbsp;&nbsp;&nbsp;说明：<br/>
     * &nbsp;&nbsp;&nbsp;&nbsp;xls格式类型的读取
     * </p>
     * @param path
     *        	导出的excel文件路径（包含了文件名）
     * @param sheetName
     *          导出的excel表单名
     * @return 表头
     */
    private String[] readExcelLowVersionHeader(String path, String sheetName) {
        String[] header = null;
        File file = new File(path);
        if (file.exists()) {
            HSSFWorkbook xwb = null;
            try {
                xwb = new HSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                e.printStackTrace();
            }
            int index = 0;
            if (sheetName != "") {
                index = xwb.getSheetIndex(sheetName);
            } else
                index = 0;
            if (index != -1) {
                HSSFSheet sheet = xwb.getSheetAt(index);
                HSSFRow row = sheet.getRow(0);
                int colNum = row.getPhysicalNumberOfCells();
                header = new String[colNum];
                for (int i = 0; i < colNum; i++) {
                    header[i] = row.getCell(i).getStringCellValue();
                }
            }
        }
        return header;
    }

    /**
     * excel文件中表头的读取
     * <p style="color:red;font-size:14px;">
     * &nbsp;&nbsp;&nbsp;&nbsp;说明：<br/>
     * &nbsp;&nbsp;&nbsp;&nbsp;xlsx格式类型的读取
     * </p>
     * @param path
     *        	导出的excel文件路径（包含了文件名）
     * @param sheetName
     *          导出的excel表单名
     * @return 表头
     */
    private String[] readExcelAdvabceVersionHeader(String path, String sheetName) {
        String[] header = null;
        File file = new File(path);
        if (file.exists()) {
            XSSFWorkbook xwb = null;
            try {
                xwb = new XSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                e.printStackTrace();
            }
            int index = 0;
            if (sheetName != "") {
                index = xwb.getSheetIndex(sheetName);
            } else
                index = 0;
            if (index != -1) {
                XSSFSheet sheet = xwb.getSheetAt(index);
                XSSFRow row = sheet.getRow(0);
                int colNum = row.getPhysicalNumberOfCells();
                header = new String[colNum];
                for (int i = 0; i < colNum; i++) {
                    header[i] = row.getCell(i).getStringCellValue();
                }
            }
        }
        return header;
    }

    /**
     * 读取解析excel文件表单的List数据对象
     * <p style="color:red;font-size:14px;">
     * &nbsp;&nbsp;&nbsp;&nbsp;说明：<br/>
     * &nbsp;&nbsp;&nbsp;&nbsp;xls格式类型的读取
     * </p>
     * @param path
     *            excel文件路径（包含了文件名）
     * @param sheetName
     *            表单名
     * @param isContainHeader
     *            是否有一行表头
     * @return Excel二维数据列表
     */
    private List<List<Object>> readExcelLowVersion(String path,
                                                   String sheetName, boolean isContainHeader) {
        List<List<Object>> dataList = new ArrayList<List<Object>>();
        File file = new File(path);
        if (file.exists()) {
            HSSFWorkbook xwb = null;
            try {
                xwb = new HSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                e.printStackTrace();
            }
            int index = 0;
            if (sheetName != "") {
                index = xwb.getSheetIndex(sheetName);
            } else
                index = 0;
            if (index != -1) {
                HSSFSheet sheet = xwb.getSheetAt(index);
                HSSFRow row = null;
                HSSFCell cell = null;
                Object val = null;
                DecimalFormat df = new DecimalFormat("0");		// 格式化数字
                SimpleDateFormat sdf = new SimpleDateFormat(
                        "yyyy-MM-dd HH:mm:ss");					// 格式化日期字符串
                int i = sheet.getFirstRowNum();
                if (isContainHeader) {
                    i++;
                }
                for (; i < sheet.getPhysicalNumberOfRows(); i++) {
                    row = sheet.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    List<Object> objList = new ArrayList<Object>();
                    for (int j = row.getFirstCellNum(); j < row
                            .getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        if (cell == null) {
                            val = null;
                            objList.add(val);
                            continue;
                        }
                        switch (cell.getCellType()) {
                            case XSSFCell.CELL_TYPE_STRING:
                                val = cell.getStringCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                if ("@".equals(cell.getCellStyle()
                                        .getDataFormatString())) {
                                    val = df.format(cell.getNumericCellValue());
                                } else if ("General".equals(cell.getCellStyle()
                                        .getDataFormatString())) {
                                    val = df.format(cell.getNumericCellValue());
                                } else {
                                    val = sdf.format(HSSFDateUtil.getJavaDate(cell
                                            .getNumericCellValue()));
                                }
                                break;
                            case XSSFCell.CELL_TYPE_BOOLEAN:
                                val = cell.getBooleanCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_BLANK:
                                val = "";
                                break;
                            default:
                                val = cell.toString();
                                break;
                        }
                        objList.add(val);
                    }
                    dataList.add(objList);
                }
            }
        }
        return dataList;
    }

    /**
     * 读取解析excel文件表单的List数据对象
     * <p style="color:red;font-size:14px;">
     * &nbsp;&nbsp;&nbsp;&nbsp;说明：<br/>
     * &nbsp;&nbsp;&nbsp;&nbsp;xlsx格式类型的读取
     * </p>
     * @param path
     *            excel文件路径（包含了文件名）
     * @param sheetName
     *            表单名
     * @param isContainHeader
     *            是否有一行表头
     * @return Excel二维数据列表
     */
    private List<List<Object>> readExcelAdvabceVersion(String path,
                                                       String sheetName, boolean isContainHeader) {
        List<List<Object>> dataList = new ArrayList<List<Object>>();
        try {
            File file = new File(path);
            if (file.exists()) {
                FileInputStream inputStream = new FileInputStream(file);
                XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
                int index = 0;
                if (sheetName == "") {
                    index = 0;
                } else
                    index = xwb.getSheetIndex(sheetName);
                if (index != -1) {
                    XSSFSheet sheet = xwb.getSheetAt(index);
                    XSSFRow row = null;
                    XSSFCell cell = null;
                    Object val = null;
                    int i = sheet.getFirstRowNum();
                    if (isContainHeader) {
                        i++;
                    }
                    for (; i < sheet.getPhysicalNumberOfRows(); i++) {
                        row = sheet.getRow(i);
                        if (row == null) {
                            continue;
                        }
                        List<Object> objList = new ArrayList<Object>();
                        row.getPhysicalNumberOfCells();
                        for (int j = row.getFirstCellNum(); j < row
                                .getLastCellNum(); j++) {
                            cell = row.getCell(j);
                            if (cell == null) {
                                val = "";
                                objList.add(val);
                                continue;
                            }
                            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                            objList.add(cell.getStringCellValue());
                        }
                        dataList.add(objList);
                    }
                }
                inputStream.close();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    /**
     * 导出列表数据带excel中
     * <p style="color:red;font-size:14px;">
     * &nbsp;&nbsp;&nbsp;&nbsp;说明：<br/>
     * &nbsp;&nbsp;&nbsp;&nbsp;xls格式类型的导出
     * </p>
     * @param path
     *        	导出的excel文件路径（包含了文件名）
     * @param sheetName
     *          导出的excel表单名
     * @param head
     * 			excel的表头
     * @param detalist
     * 			导出的具体数据
     * @return 导出结果
     */
    private boolean exportExcelLowVersion(String path, String sheetName,
                                          String[] head, List<List<String>> detalist) {
        HSSFWorkbook wk = new HSSFWorkbook();
        HSSFSheet sheet = wk.createSheet(sheetName);
        HSSFRow headrow = sheet.createRow(0);
        int index = 0;
        for (String h : head) { // 表头写入
            HSSFCell headcell = headrow.createCell(index++);
            headcell.setCellValue(h);
        }
        int rownum = detalist.size();
        for (int i = 1; i < rownum + 1; i++) {
            List<String> list = detalist.get(i - 1);
            HSSFRow row = sheet.createRow(i);
            for (int cindex = 0; cindex < list.size(); cindex++) {
                HSSFCell cell = row.createCell(cindex);
                cell.setCellValue(list.get(cindex));
            }
        }
        OutputStream out = null;
        try {
            out = new FileOutputStream(path);
            wk.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 导出列表数据带excel中
     * <p style="color:red;font-size:14px;">
     * &nbsp;&nbsp;&nbsp;&nbsp;说明：<br/>
     * &nbsp;&nbsp;&nbsp;&nbsp;xlsx格式类型的导出
     * </p>
     * @param path
     *        	导出的excel文件路径（包含了文件名）
     * @param sheetName
     *          导出的excel表单名
     * @param head
     * 			excel的表头
     * @param detalist
     * 			导出的具体数据
     * @return 导出结果
     */
    private boolean exportExcelAdvanceVersion(String path, String sheetName,
                                              String[] head, List<List<String>> detalist) {
        XSSFWorkbook wk = new XSSFWorkbook();
        XSSFSheet sheet = wk.createSheet(sheetName);
        XSSFRow headrow = sheet.createRow(0);
        int index = 0;
        for (String h : head) { // 表头写入
            XSSFCell headcell = headrow.createCell(index++);
            headcell.setCellValue(h);
        }
        int rownum = detalist.size();
        for (int i = 1; i < rownum + 1; i++) {
            List<String> list = detalist.get(i - 1);
            XSSFRow row = sheet.createRow(i);
            for (int cindex = 0; cindex < list.size(); cindex++) {
                XSSFCell cell = row.createCell(cindex);
                cell.setCellValue(list.get(cindex));
            }
        }
        OutputStream out = null;
        try {
            out = new FileOutputStream(path);
            wk.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return false;
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }


    /**
     * 取得文件扩展名
     *
     * @param filename
     * 			文件名
     * @return 文件扩展名(不含.)
     */
    private String getExtensionName(String filename) {
        if ((filename != null) && (filename.length() > 0)) {
            int dot = filename.lastIndexOf('.');
            if ((dot > -1) && (dot < (filename.length() - 1))) {
                return filename.substring(dot + 1);
            }
        }
        return filename;
    }

    /**
     * 复写该方法可以用来将实体根据需要转换成能用于导出的数据类型
     *
     * @param o
     * 			数据库实体列表
     * @param header
     * 			表头
     * @return 可导出的Excel二维数据
     */
    public abstract List<List<String>> getDetailByListinfo(List<T> o,
                                                           String[] header);

    /**
     * 复写该方法可以用来将Excel的数据类型转换为数据库实体列表
     *
     * @param objectList
     * 			excel的二维表数据
     * @param header
     * 			表头
     * @return 数据库实体列表
     */
    public abstract List<T> convertDetailToListinfo(
            List<List<Object>> objectList, String[] header);

    /**
     * 补全实体
     *
     * @param infoList
     * 			实体列表
     * @param s
     * 			填充的数据源
     * @return 返回充填后的实体
     */
    public abstract List<T> calculateOtherInfo(List<T> infoList, S s);
}

